﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using ebay.Dal.CenterDB_OrderTableAdapters;
using System.Data;
using ebay.Utility;
using LumenWorks.Framework.IO.Csv;
using System.Globalization;
using ebay.Dal.CenterDB_BasTableAdapters;
using ebay.Dal.CenterDB_EbayTableAdapters;

namespace ebay.Dal
{
    public class PPOrder
    {
        public bool Add(string ebayUserId, List<ebay.Model.PPOrder> listOrders)
        {
            string cmdText1 = "Select cast(StatusId as varchar) + ToInvoice + ToPz as StatusId From PPOrder Where TransactionId = '{0}' And EbayUserId = '{1}' And CreateDateTime = '{2}'";
            string cmdText3 = "Delete From PPOrder Where TransactionId = '{0}' And EbayUserId = '{1}' And CreateDateTime = '{2}'";
            using (SqlConnection connection = new SqlConnection(SqlHelper.ceterDB_Conn))
            {
                connection.Open();

                T_PPOrderTableAdapter orderAdapter = new T_PPOrderTableAdapter();
                orderAdapter.Connection = connection;
                using (SqlTransaction trans = connection.BeginTransaction())
                {
                    orderAdapter.Transaction = trans;
                    foreach (ebay.Model.PPOrder eOrders in listOrders)
                    {
                        try
                        {

                            //查询本条订单在数据库是否存在,如果存在则会返回改订单的状态
                            object val = SqlHelper.ExecuteScalar(trans, CommandType.Text,
                                string.Format(cmdText1, eOrders.TransactionID, eOrders.eBayUserID, eOrders.CreateDateTime),
                                null);

                            if (val != null && val != DBNull.Value)
                            {

                                //如果该订单的状态为初始状态,则删除
                                if (val.ToString() == "1NN")
                                {
                                    SqlHelper.ExecuteNonQuery(trans, CommandType.Text,
                                    string.Format(cmdText3, eOrders.TransactionID, eOrders.eBayUserID, eOrders.CreateDateTime),
                                    null);
                                }

                                //否则跳过,不执行插入动作
                                else
                                {
                                    continue;
                                }
                            }

                            //新增订单记录
                            orderAdapter.Insert(Guid.NewGuid(), eOrders.eBayUserID, eOrders.TransactionID, eOrders.CreateDateTime, eOrders.TimeZone, eOrders.Name, eOrders.Type,
                                eOrders.Status, eOrders.Subject, eOrders.Currency, eOrders.Gross, eOrders.Fee, eOrders.Net, eOrders.Reserve, eOrders.Note,
                                eOrders.FromEmailAddress, eOrders.ToEmailAddress, eOrders.PaymentType, eOrders.CounterpartyStatus, eOrders.PostalAddress,
                                eOrders.AddressStatus, eOrders.ItemTitle, eOrders.ItemID, eOrders.PostageandPackingAmount, eOrders.InsuranceAmount,
                                eOrders.VAT, eOrders.Option1Name, eOrders.Option1Value, eOrders.Option2Name, eOrders.Option2Value, eOrders.AuctionSite,
                                eOrders.BuyerID, eOrders.ItemURL, eOrders.ClosingDate, eOrders.ReferenceTxnID, eOrders.InvoiceNumber,
                                eOrders.SubscriptionNumber, eOrders.CustomNumber, eOrders.ReceiptID, eOrders.Balance, eOrders.AddressLine1,
                                eOrders.AddressLine2, eOrders.City, eOrders.County, eOrders.PostalCode, eOrders.Country, eOrders.ContactPhoneNumber,
                                eOrders.BalanceImpact, eOrders.StatusId, eOrders.ImportDate, eOrders.MatchDate, eOrders.ToInvoice, eOrders.ToPz);
                        }
                        catch (Exception ex)
                        {
                            FileLog.CreateLog(LogAction.Error, ebayUserId, "PPOrder Insert失败 ...");
                            FileLog.CreateLog(LogAction.Error, ebayUserId, "原因：" + ex.Message);
                            FileLog.CreateLog(LogAction.Error, ebayUserId, "错误实体：");
                            FileLog.CreateLog(LogAction.Error, ebayUserId, XmlHelper.XmlSerializer<ebay.Model.PPOrder>(eOrders));
                            return false;
                        }
                    }
                    try
                    {
                        trans.Commit();
                        return true;
                    }
                    catch (Exception ex)
                    {
                        FileLog.CreateLog(LogAction.Error, ebayUserId, "PPOrder 事务提交失败 ...");
                        FileLog.CreateLog(LogAction.Error, ebayUserId, "原因：" + ex.Message);
                        trans.Rollback();
                    }
                    return false;
                }
            }
        }

        public bool WriteVoucher(DateTime dt)
        {
            SqlParameter[] p = { 
                               new SqlParameter("@FDate",SqlDbType.VarChar)
                               };
            using (SqlConnection connection = new SqlConnection(SqlHelper.ceterDB_Conn))
            {
                connection.Open();
                using (SqlTransaction trans = connection.BeginTransaction())
                {
                    p[0].Value = dt.ToString("yyyyMMdd");
                    try
                    {
                        SqlHelper.ExecuteNonQuery(trans, CommandType.StoredProcedure, "sp_Ins_PZ", 240, p);
                    }
                    catch
                    {
                        throw;
                    }
                    try
                    {
                        trans.Commit();
                    }
                    catch
                    {
                        trans.Rollback();
                    }
                }
            }

            return true;
        }

        public string GetQueryInfo(string PPOrderName, string PPInfoColName)
        {

            SqlParameter[] p = { 
                               new SqlParameter("@PPOrderName",SqlDbType.VarChar),
                               new SqlParameter("@PPInfoColName",SqlDbType.VarChar),
                               new SqlParameter("@sql",SqlDbType.VarChar,8000)
                               };
            p[0].Value = PPOrderName;
            p[1].Value = PPInfoColName;
            p[2].Direction = ParameterDirection.Output;
            try
            {
                SqlHelper.ExecuteNonQuery(SqlHelper.ceterDB_Conn, CommandType.StoredProcedure, "sp_get_match_text", p);
                return p[2].Value.ToString();
            }
            catch
            {
                return "1 = 2";
            }
        }
        string GetString(Dictionary<string, string> keyValue, string key, CsvReader csv)
        {
            return keyValue[key] == "" ? "" : csv[keyValue[key]];
        }

        DateTime GetDateTime(string text, string formatter)
        {
            DateTime val = new DateTime(1900, 1, 1);
            if (text == "")
                return val;

            try
            {
                val = DateTime.ParseExact(text, formatter, CultureInfo.InvariantCulture);
            }
            catch
            {
                val = DateTime.Parse(text);
            }
            return val;
        }

        decimal GetResule(string val)
        {
            val = val.Replace(",", "");
            decimal result = 0m;
            if (decimal.TryParse(val, out result))
            {
                return result;
            }
            return 0m;
        }

        string GetPayPalAccount(string type, string toMail, string FromMail)
        {
            switch (type.Trim())
            {
                case "Payment Sent":
                    return FromMail;
                case "Preapproved Payment Sent":
                    return FromMail;
                case "Pre-approved Payment Sent":
                    return FromMail;
                case "PayPal Express Checkout Payment Sent":
                    return FromMail;
                default:
                    return toMail;
            }
        }

        public DataTable CreateTable(CsvReader csv, CsvReader csvclone)
        {
            DataTable ppOrders = new DataTable("PPOrder_L");
            ppOrders.Columns.Add("EbayUserId", typeof(string));
            ppOrders.Columns.Add("TransactionId", typeof(string));
            ppOrders.Columns.Add("CreateDateTime", typeof(DateTime));
            ppOrders.Columns.Add("TimeZone", typeof(string));
            ppOrders.Columns.Add("Name", typeof(string));
            ppOrders.Columns.Add("Type", typeof(string));
            ppOrders.Columns.Add("Status", typeof(string));
            ppOrders.Columns.Add("Subject", typeof(string));
            ppOrders.Columns.Add("Currency", typeof(string));
            ppOrders.Columns.Add("Gross", typeof(decimal));
            ppOrders.Columns.Add("Fee", typeof(decimal));
            ppOrders.Columns.Add("Net", typeof(decimal));
            ppOrders.Columns.Add("Reserve", typeof(string));
            ppOrders.Columns.Add("Note", typeof(string));
            ppOrders.Columns.Add("FromEmailAddress", typeof(string));
            ppOrders.Columns.Add("ToEmailAddress", typeof(string));
            ppOrders.Columns.Add("PaymentType", typeof(string));
            ppOrders.Columns.Add("CounterpartyStatus", typeof(string));
            ppOrders.Columns.Add("PostalAddress", typeof(string));
            ppOrders.Columns.Add("AddressStatus", typeof(string));
            ppOrders.Columns.Add("ItemTitle", typeof(string));
            ppOrders.Columns.Add("ItemID", typeof(string));
            ppOrders.Columns.Add("PostageandPackingAmount", typeof(decimal));
            ppOrders.Columns.Add("InsuranceAmount", typeof(decimal));
            ppOrders.Columns.Add("VAT", typeof(decimal));
            ppOrders.Columns.Add("Option1Name", typeof(string));
            ppOrders.Columns.Add("Option1Value", typeof(string));
            ppOrders.Columns.Add("Option2Name", typeof(string));
            ppOrders.Columns.Add("Option2Value", typeof(string));
            ppOrders.Columns.Add("AuctionSite", typeof(string));
            ppOrders.Columns.Add("BuyerID", typeof(string));
            ppOrders.Columns.Add("ItemURL", typeof(string));
            ppOrders.Columns.Add("ClosingDate", typeof(DateTime));
            ppOrders.Columns.Add("ReferenceTxnID", typeof(string));
            ppOrders.Columns.Add("InvoiceNumber", typeof(string));
            ppOrders.Columns.Add("SubscriptionNumber", typeof(string));
            ppOrders.Columns.Add("CustomNumber", typeof(string));
            ppOrders.Columns.Add("ReceiptID", typeof(string));
            ppOrders.Columns.Add("Balance", typeof(decimal));
            ppOrders.Columns.Add("AddressLine1", typeof(string));
            ppOrders.Columns.Add("AddressLine2", typeof(string));
            ppOrders.Columns.Add("City", typeof(string));
            ppOrders.Columns.Add("County", typeof(string));
            ppOrders.Columns.Add("PostalCode", typeof(string));
            ppOrders.Columns.Add("Country", typeof(string));
            ppOrders.Columns.Add("ContactPhoneNumber", typeof(string));
            ppOrders.Columns.Add("BalanceImpact", typeof(string));
            ppOrders.Columns.Add("StatusId", typeof(string));
            ppOrders.Columns.Add("ImportDate", typeof(DateTime));
            ppOrders.Columns.Add("MatchDate", typeof(DateTime));
            ppOrders.Columns.Add("ToInvoice", typeof(string));
            ppOrders.Columns.Add("ToPz", typeof(string));

            
            //获取PayPal帐号信息
            string paypalAccountId = "";
            while (csvclone.ReadNextRecord())
            {
                paypalAccountId = GetPayPalAccount(csvclone["Type"], csvclone["To Email Address"], csvclone["From Email Address"]);
                if (!string.IsNullOrEmpty(paypalAccountId))
                {
                    break;
                }
            }
            if (string.IsNullOrEmpty(paypalAccountId))
            {
                throw new Exception("未找到To Email Address对应的PayPal帐号");
            }
            //初始化帐号信息
            Dictionary<string, string> account = new Dictionary<string, string>();
            T_EbayPaypalAccountRelationTableAdapter pacc = new T_EbayPaypalAccountRelationTableAdapter();
            DataTable acctable = pacc.GetData();
            foreach (DataRow r in acctable.Rows)
            {
                account.Add(r[0].ToString(), r[1].ToString());
            }

            if (!account.ContainsKey(paypalAccountId))
            {
                throw new Exception("未设置PayPal帐号：" + paypalAccountId + " 的对应的关系");
            }
            string ebayUserId = account[paypalAccountId];

            //初始化数据字典信息
            Dictionary<string, string> keyValue = new Dictionary<string, string>();
            T_PPDictionaryTableAdapter ppd = new T_PPDictionaryTableAdapter();
            DataTable ppdtable = ppd.GetDataByEbayUserId(paypalAccountId);
            if (ppdtable.Rows.Count == 0)
            {
                throw new Exception("未设置PayPal帐号：" + paypalAccountId + " 的数据字典");
            }
            foreach (DataRow r in ppdtable.Rows)
            {
                keyValue.Add(r[1].ToString(), r[2].ToString());
            }

            //读取数据
            DateTime importDate = DateTime.Now;
            while (csv.ReadNextRecord())
            {
                string date = GetString(keyValue, "Date", csv);
                string time = GetString(keyValue, "Time", csv);
                string cdate = GetString(keyValue, "Closing Date", csv);

                string formattext = "dd/MM/yyyy HH:mm:ss";
                date = date.Replace("-", "/");

                DateTime createDate = GetDateTime(date + " " + time, formattext);


                formattext = "dd/MM/yyyy";

                cdate = cdate.Replace("-", "/");
                DateTime ClosingDate = GetDateTime(cdate, formattext);

                string type = GetString(keyValue, "Type", csv);

                paypalAccountId = GetPayPalAccount(type, GetString(keyValue, "To Email Address", csv), GetString(keyValue, "From Email Address", csv));

                if (!string.IsNullOrEmpty(paypalAccountId))
                {
                    if (!account.ContainsKey(paypalAccountId))
                    {
                        throw new Exception("未设置PayPal帐号：" + paypalAccountId + " 的对应的关系");
                    }
                    ebayUserId = account[paypalAccountId];
                }

                ppOrders.Rows.Add(
                    ebayUserId,
                    GetString(keyValue, "Transaction ID", csv),
                    createDate,
                    GetString(keyValue, "Time Zone", csv),
                    GetString(keyValue, "Name", csv),
                    type,
                    GetString(keyValue, "Status", csv),
                    GetString(keyValue, "Subject", csv),
                    GetString(keyValue, "Currency", csv),
                    GetResule(GetString(keyValue, "Gross", csv)),
                    GetResule(GetString(keyValue, "Fee", csv)),
                    GetResule(GetString(keyValue, "Net", csv)),
                    GetString(keyValue, "Reserve", csv),
                    GetString(keyValue, "Note", csv),
                    GetString(keyValue, "From Email Address", csv),
                    GetString(keyValue, "To Email Address", csv),
                    GetString(keyValue, "Payment Type", csv),
                    GetString(keyValue, "Counterparty Status", csv),
                    GetString(keyValue, "Postal Address", csv),
                    GetString(keyValue, "Address Line 2/District/Neighbourhood", csv),
                    GetString(keyValue, "Item Title", csv),
                    GetString(keyValue, "Item ID", csv),
                    GetResule(GetString(keyValue, "Postage and Packing Amount", csv)),
                    GetResule(GetString(keyValue, "Insurance Amount", csv)),
                    GetResule(GetString(keyValue, "VAT", csv)),
                    GetString(keyValue, "Option 1 Name", csv),
                    GetString(keyValue, "Option 1 Value", csv),
                    GetString(keyValue, "Option 2 Name", csv),
                    GetString(keyValue, "Option 2 Value", csv),
                    GetString(keyValue, "Auction Site", csv),
                    GetString(keyValue, "Buyer ID", csv),
                    GetString(keyValue, "Item URL", csv),
                    ClosingDate,
                    GetString(keyValue, "Reference Txn ID", csv),
                    GetString(keyValue, "Invoice Number", csv),
                    GetString(keyValue, "Subscription Number", csv),
                    GetString(keyValue, "Custom Number", csv),
                    GetString(keyValue, "Receipt ID", csv),
                    GetResule(GetString(keyValue, "Balance", csv)),
                    GetString(keyValue, "Address Line 1", csv),
                    GetString(keyValue, "Address Line 2/District/Neighbourhood", csv),
                    GetString(keyValue, "Town/City", csv),
                    GetString(keyValue, "State/Province/Region/County/Territory/Prefecture/Republic", csv),
                    GetString(keyValue, "Postcode", csv),
                    GetString(keyValue, "Country", csv),
                    GetString(keyValue, "Contact Phone Number", csv),
                    GetString(keyValue, "Balance Impact", csv),
                    1,
                    importDate,
                    new DateTime(1900, 1, 1),
                    'N',
                    'N');
            }
            return ppOrders;
        }


        public bool WriteToServer(CsvReader csv,CsvReader csvclone, ref int ImportCount)
        {
            DataTable newProducts = CreateTable(csv, csvclone);
            using (SqlConnection connection = new SqlConnection(SqlHelper.ceterDB_Conn))
            {
                connection.Open();

                using (SqlTransaction trans = connection.BeginTransaction())
                {

                    using (SqlBulkCopy bulkCopy = new SqlBulkCopy(connection, SqlBulkCopyOptions.Default, trans))
                    {
                        foreach (DataColumn dc in newProducts.Columns)
                        {
                            bulkCopy.ColumnMappings.Add(dc.ColumnName, dc.ColumnName);

                        }
                        bulkCopy.DestinationTableName = "dbo.PPOrder_L";

                        try
                        {
                            bulkCopy.WriteToServer(newProducts);
                            ImportCount = SqlHelper.ExecuteNonQuery(trans, CommandType.StoredProcedure, "sp_import_pporder", null);
                            trans.Commit();
                        }
                        catch (Exception ex)
                        {
                            FileLog.CreateLog(LogAction.Error, "PayPal导入", ex.Message);
                            trans.Rollback();
                            throw ex;
                        }
                    }

                }
            }
            return true;
        }

        public DataTable CreateXOUTTable(CsvReader csv, CsvReader csvclone)
        {
            DataTable XOUTBill = new DataTable("RedXOUTBill");
            XOUTBill.Columns.Add("Account", typeof(string));
            XOUTBill.Columns.Add("orderid", typeof(string));
            XOUTBill.Columns.Add("parcelid", typeof(string));     
          
            ////初始化数据字典信息
            //Dictionary<string, string> keyValue = new Dictionary<string, string>();

            //foreach (DataRow r in XOUTBill.Rows)
            //{
            //    keyValue.Add(r[1].ToString(), r[2].ToString());
            //}

            //读取数据
            DateTime importDate = DateTime.Now;
            while (csv.ReadNextRecord())
            {
                string Account = csv["Account"];
                string OrderNumber = csv["orderid"];
                string ParcelID = csv["parcelid"];

                XOUTBill.Rows.Add(
                    Account,
                    OrderNumber,
                    ParcelID
                   );
            }
            return XOUTBill;
        }
        public bool WriteToK3XOUT(CsvReader csv, CsvReader csvclone, ref int ImportCount)
        {
            DataTable newProducts = CreateXOUTTable(csv, csvclone);
            using (SqlConnection connection = new SqlConnection(SqlHelper.ceterDB_Conn))
            {
                connection.Open();

                using (SqlTransaction trans = connection.BeginTransaction())
                {

                    using (SqlBulkCopy bulkCopy = new SqlBulkCopy(connection, SqlBulkCopyOptions.Default, trans))
                    {
                        foreach (DataColumn dc in newProducts.Columns)
                        {
                            bulkCopy.ColumnMappings.Add(dc.ColumnName, dc.ColumnName);

                        }
                        bulkCopy.DestinationTableName = "dbo.RedXOUTBill";

                        try
                        {
                            bulkCopy.WriteToServer(newProducts);
                            ImportCount = SqlHelper.ExecuteNonQuery(trans, CommandType.StoredProcedure, "p_RedXOUTBill", null);
                            trans.Commit();
                        }
                        catch (Exception ex)
                        {
                            FileLog.CreateLog(LogAction.Error, "红字销售出库单导入", ex.Message);
                            trans.Rollback();
                            throw ex;
                        }
                    }

                }
            }
            return true;
        }
    }

    //public class p
    //{
    //    public void add()
    //    {
    //        string rowID = "";
    //        string b = "upRow('" + rowID + "')";
    //    }
    //}
}

